﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Odbc;
using Org.SqlHelper;
using QualityRpt.Utils;
using System.Web.Caching;
using System.Web;
using log4net;

namespace QualityRpt.Dao.Db2
{
    class TransLBDao:ITransLBDao
    {
        static ILog logger = LogManager.GetLogger("TransLBDao");
        const string cachePrefix = "TRLB-";
        /// <summary>
        /// 低电标签综合查询
        /// </summary>
        public DataTable GetOBUStatByQ(DateTime startTime, DateTime endTime, int statTType, string spin, string specin, string cnin,
            string rdin, string plazain, string lanein, bool fromTemp, string[] numstartArray, string[] numendArray)
        {
            DateTime trackStart = DateTime.Now;
            DataTable rtTable = null;
            StringBuilder sbSql = new StringBuilder();//整个获得低电标签个数的sql语句
            StringBuilder sbDWhere = new StringBuilder();//放在外层的条件
            StringBuilder sbSWhere = new StringBuilder();//放在子查询的条件
            StringBuilder sbDGroup = new StringBuilder();
            StringBuilder sbSGroup = new StringBuilder();
            StringBuilder sbDSelect = new StringBuilder();
            StringBuilder sbSSelect = new StringBuilder();
            #region 处理条件和分组
            if (spin != null && spin.Trim() != string.Empty)
            {
                sbDGroup.Append("D.SUPPLIER_NAME,");
                sbDSelect.Append("D.SUPPLIER_NAME,");
                if (sbDWhere.Length > 0)
                {
                    sbDWhere.Append(" AND ");
                }
                sbDWhere.Append(" D.SUPPLIER_ID IN (" + spin + ")");
            }
            if (specin != null && specin.Trim() != string.Empty)
            {
                sbDGroup.Append("D.SPEC_NAME,");
                sbDSelect.Append("D.SPEC_NAME,");
                if (sbDWhere.Length > 0)
                {
                    sbDWhere.Append(" AND ");
                }
                sbDWhere.Append(" D.SPEC_ID IN (" + specin + ")");
            }
            if (cnin != null && cnin.Trim() != string.Empty)
            {
                sbDGroup.Append("D.CONTRACT_NO,");
                sbDSelect.Append("D.CONTRACT_NO,");
                if (sbDWhere.Length > 0)
                {
                    sbDWhere.Append(" AND ");
                }
                sbDWhere.Append(" D.CONTRACT_NO IN (" + StringUtils.FormatStrInSql(cnin) + ")");
            }
			sbSWhere.Append("TR.TRANSOCCURTIME>='" + startTime.ToString("yyyy-MM-dd HH:mm:ss") + "' AND TR.TRANSOCCURTIME<='" + endTime.ToString("yyyy-MM-dd HH:mm:ss") + "' AND TR.LANETYPE =0 AND TR.DATASOURCE = 1 ");
			if (numstartArray != null && numstartArray.Length > 0)
			{
				sbSWhere.Append(" AND (");
				for (int i = 0; i < numstartArray.Length; i++)
				{
					if (i > 0)
					{
						sbSWhere.Append(" OR ");
					}
					sbSWhere.Append("(TR.OBUNO>='" + numstartArray[i] + "' AND TR.OBUNO<='" + numendArray[i] + "')");
				}
				if (fromTemp)
				{
					sbSWhere.Append(" OR TR.OBUNO IN (SELECT OBUNO FROM RPT.TEMP_OBU)");
				}
				sbSWhere.Append(")");
				sbDGroup.Append("U.OBUNO,"); sbDSelect.Append("U.OBUNO,");
			}
            if (sbDGroup.Length < 1)
            {
                sbDGroup.Append("D.SUPPLIER_NAME,D.SPEC_NAME,");
                sbDSelect.Append("D.SUPPLIER_NAME,D.SPEC_NAME,");
            }
            //处理按时间分组的情况
            if (statTType > 0)
            {
                switch (statTType)
                {
                    case 1://按月
                        sbDGroup.Append("U.STYEAR,U.STMONTH,"); sbDSelect.Append("U.STYEAR,U.STMONTH,");
                        sbSGroup.Append("YEAR(TR.TRANSOCCURTIME),MONTH(TR.TRANSOCCURTIME),");
                        sbSSelect.Append("YEAR(TR.TRANSOCCURTIME) AS STYEAR,MONTH(TR.TRANSOCCURTIME) AS STMONTH,");
                        break;
                    case 2://按周
                        sbDGroup.Append("U.STYEAR,U.STWEEK,"); sbDSelect.Append("U.STYEAR,U.STWEEK,");
                        sbSGroup.Append("YEAR(TR.TRANSOCCURTIME),WEEK_ISO(TR.TRANSOCCURTIME),");
                        sbSSelect.Append("YEAR(TR.TRANSOCCURTIME) AS STYEAR,WEEK_ISO(TR.TRANSOCCURTIME) AS STWEEK,");
                        break;
                    case 3://按日
                        sbDGroup.Append("U.STYEAR,U.STMONTH,U.STDAY,"); sbDSelect.Append("U.STYEAR,U.STMONTH,U.STDAY,");
                        sbSGroup.Append("YEAR(TR.TRANSOCCURTIME),MONTH(TR.TRANSOCCURTIME),DAY(TR.TRANSOCCURTIME),");
                        sbSSelect.Append("YEAR(TR.TRANSOCCURTIME) AS STYEAR,MONTH(TR.TRANSOCCURTIME) AS STMONTH,DAY(TR.TRANSOCCURTIME) AS STDAY,");
                        break;
                    case 4://按时
                        sbDGroup.Append("U.STYEAR,U.STMONTH,U.STDAY,U.STHOUR,"); sbDSelect.Append("U.STYEAR,U.STMONTH,U.STDAY,U.STHOUR,");
                        sbSGroup.Append("YEAR(TR.TRANSOCCURTIME),MONTH(TR.TRANSOCCURTIME),DAY(TR.TRANSOCCURTIME),HOUR(TR.TRANSOCCURTIME),");
                        sbSSelect.Append("YEAR(TR.TRANSOCCURTIME) AS STYEAR,MONTH(TR.TRANSOCCURTIME) AS STMONTH,DAY(TR.TRANSOCCURTIME) AS STDAY,HOUR(TR.TRANSOCCURTIME) AS STHOUR,");
                        break;
                }
            }
            
            if (rdin != null && rdin.Trim() != string.Empty)
            {
                sbSWhere.Append(" AND A.ROAD_ID IN (" + rdin + ")");
            }
            if (plazain != null && plazain.Trim() != string.Empty)
            {
                sbSWhere.Append(" AND TR.PLAZAID IN (" + plazain + ")");
            }
            if (lanein != null && lanein.Trim() != string.Empty)
            {
                sbSWhere.Append(" AND TR.LANENO IN (" + lanein + ")");
            }
            #endregion
            sbSql.Append("WITH U AS (SELECT " + sbSSelect.ToString() + " TR.OBUNO FROM RPT.TRANS_LB AS TR ");
            if (sbSWhere.ToString().Contains("A."))
            {
                sbSql.Append(" LEFT JOIN RPT.AIRWIRE_PLAZA AS A ON TR.PLAZAID=A.PLAZA_ID AND TR.LANENO=A.LANE_NO ");
            }
            sbSql.Append(" WHERE " + sbSWhere.ToString() + " GROUP BY " + sbSGroup.ToString() + "TR.OBUNO) SELECT " + sbDSelect.ToString() 
            + " COUNT(DISTINCT U.OBUNO) AS TRANSDCOUNT FROM U LEFT JOIN RPT.DEVICE_CONTRACT_DETAIL AS D ON D.START_NO<=U.OBUNO AND U.OBUNO<=D.END_NO ");
            if (sbDWhere.Length > 0)
            {
                sbSql.Append(" WHERE " + sbDWhere.ToString());
            }
            sbSql.Append(" GROUP BY " + sbDGroup.ToString());
            sbSql.Remove(sbSql.Length - 1, 1);
            string cachekey = cachePrefix  + sbSql.ToString();
            if (CacheManager.Get(cachekey) == null)
            {
                using (OdbcConnection conn = ODBCHelper.GetConnection())
                {
                    rtTable = ODBCHelper.ReadTable(conn, CommandType.Text, sbSql.ToString());
                }
                CacheManager.Put(cachekey, rtTable);
            }
            else
            {
                rtTable = CacheManager.Get(cachekey) as DataTable;
            }
            TimeSpan tsConsume = DateTime.Now - trackStart;
            logger.Info(sbSql.ToString()+"耗时:"+tsConsume.TotalSeconds+"s");
            return rtTable;
        }
        public DataTable GetDetailByQ(DateTime startTime, DateTime endTime, string spin, string specin, string cnin, string rdin,
            string plazain, string lanein, bool fromTempTable, string[] numstartArray, string[] numendArray, PageUtils pu)
        {
            DataTable rtTable = null;
            StringBuilder sbSql = new StringBuilder();//整个sql
            StringBuilder sbCountSql = new StringBuilder();
            StringBuilder sbWhere = new StringBuilder();//where sql
            sbWhere.Append("TR.TRANSOCCURTIME>='" + startTime.ToString("yyyy-MM-dd HH:mm:ss") + "' AND TR.TRANSOCCURTIME<='" + endTime.ToString("yyyy-MM-dd HH:mm:ss") + "' AND TR.LANETYPE =0 AND TR.DATASOURCE = 1 ");
            if (numstartArray != null && numstartArray.Length > 0)
            {
                sbWhere.Append(" AND (");
                for (int i = 0; i < numstartArray.Length; i++)
                {
                    if (i > 0)
                    {
                        sbWhere.Append(" OR ");
                    }
                    sbWhere.Append("(TR.OBUNO>='" + numendArray[i] + "' AND TR.OBUNO<='" + numendArray[i] + "')");
                }
                if (fromTempTable)
                {
                    sbWhere.Append(" OR TR.OBUNO IN (SELECT OBUNO FROM RPT.TEMP_OBU)");
                }
                sbWhere.Append(")");
            }
            if (spin != null && spin.Trim() != string.Empty)
            {
                sbWhere.Append(" AND D.SUPPLIER_ID IN (" + spin + ")");
            }
            if (specin != null && specin.Trim() != string.Empty)
            {
                sbWhere.Append(" AND D.SPEC_ID IN (" + specin + ")");
            }
            if (rdin != null && rdin.Trim() != string.Empty)
            {
                sbWhere.Append(" AND A.ROAD_ID IN (" + rdin + ")");
            }
            if (plazain != null && plazain.Trim() != string.Empty)
            {
                sbWhere.Append(" AND TR.PLAZAID IN (" + plazain + ")");
            }
            if (lanein != null && lanein.Trim() != string.Empty)
            {
                sbWhere.Append(" AND TR.LANENO IN (" + lanein + ")");
            }
            sbSql.Append("SELECT D.SUPPLIER_NAME,D.SPEC_NAME,TR.OBUNO,TR.OBUSTATUS,TR.TRANSOCCURTIME,A.PLAZA_NAME,TR.LANENO FROM RPT.TRANS_LB AS TR INNER JOIN RPT.DEVICE_CONTRACT_DETAIL AS D ON D.START_NO<=TR.OBUNO AND TR.OBUNO<=D.END_NO ");
            sbSql.Append(" LEFT JOIN RPT.AIRWIRE_PLAZA AS A ON TR.PLAZAID=A.PLAZA_ID AND TR.LANENO=A.LANE_NO ");
            sbSql.Append(" WHERE " + sbWhere.ToString());
            sbCountSql.Append("WITH WT AS ("+sbSql.ToString()+") SELECT COUNT(*) FROM WT");
            sbSql.Append(" ORDER BY TR.TRANSOCCURTIME ASC");
            string cacheKey = cachePrefix + sbSql.ToString();
            if (pu != null)
            {
                pu.ListSql = sbSql.ToString();
                pu.TotalCountSql = sbCountSql.ToString();
                cacheKey = cacheKey + "-" + pu.Page;
                if (CacheManager.Get(cacheKey) == null)
                {
                    rtTable = pu.GetDb2OdbcPageList();
                    CacheManager.Put(cacheKey, rtTable);
                }
                else
                {
                    rtTable = CacheManager.Get(cacheKey) as DataTable;
                    pu.DB2OdbcExecutePage();
                }
            }
            else
            {
                if (CacheManager.Get(cacheKey) == null)
                {
                    using (OdbcConnection conn = ODBCHelper.GetConnection())
                    {
                        rtTable = ODBCHelper.ReadTable(conn, CommandType.Text, sbSql.ToString());
                    }
                    CacheManager.Put(cacheKey, rtTable);
                }
                else
                {
                    rtTable = CacheManager.Get(cacheKey) as DataTable;
                }
            }
            return rtTable;
        }
        /// <summary>
        /// 获取以obu分组的交易明细 v1.2
        /// </summary>
        public DataTable GetOBUGDetailByQ(DateTime startTime, DateTime endTime, string spin, string specin, string cnin
           , string rdin, string plazain, string lanein, bool fromTempTable, string[] numstartArray, string[] numendArray, PageUtils pu)
        {
            DataTable rtTable = null;
            StringBuilder sbSql = new StringBuilder();//整个sql
            StringBuilder sbCountSql = new StringBuilder();
            StringBuilder sbWhere = new StringBuilder();//where sql
            sbWhere.Append("TR.TRANSOCCURTIME>='" + startTime.ToString("yyyy-MM-dd HH:mm:ss") + "' AND TR.TRANSOCCURTIME<='" + endTime.ToString("yyyy-MM-dd HH:mm:ss") + "' AND TR.LANETYPE =0 AND TR.DATASOURCE = 1 ");
            if (numstartArray != null && numstartArray.Length > 0)
            {
                sbWhere.Append(" AND (");
                for (int i = 0; i < numstartArray.Length; i++)
                {
                    if (i > 0)
                    {
                        sbWhere.Append(" OR ");
                    }
                    sbWhere.Append("(TR.OBUNO>='" + numendArray[i] + "' AND TR.OBUNO<='" + numendArray[i] + "')");
                }
                if (fromTempTable)
                {
                    sbWhere.Append(" OR TR.OBUNO IN (SELECT OBUNO FROM RPT.TEMP_OBU)");
                }
                sbWhere.Append(")");
            }
            if (spin != null && spin.Trim() != string.Empty)
            {
                sbWhere.Append(" AND D.SUPPLIER_ID IN (" + spin + ")");
            }
            if (specin != null && specin.Trim() != string.Empty)
            {
                sbWhere.Append(" AND D.SPEC_ID IN (" + specin + ")");
            }
            if (rdin != null && rdin.Trim() != string.Empty)
            {
                sbWhere.Append(" AND A.ROAD_ID IN (" + rdin + ")");
            }
            if (plazain != null && plazain.Trim() != string.Empty)
            {
                sbWhere.Append(" AND TR.PLAZAID IN (" + plazain + ")");
            }
            if (lanein != null && lanein.Trim() != string.Empty)
            {
                sbWhere.Append(" AND TR.LANENO IN (" + lanein + ")");
            }
            sbSql.Append("SELECT D.SUPPLIER_NAME,D.SPEC_NAME,TR.OBUNO,SUM(1) AS TRANSCOUNT FROM RPT.TRANS_LB AS TR LEFT JOIN RPT.DEVICE_CONTRACT_DETAIL AS D ON D.START_NO<=TR.OBUNO AND TR.OBUNO<=D.END_NO ");
			sbSql.Append(" LEFT JOIN RPT.AIRWIRE_PLAZA AS A ON TR.PLAZAID=A.PLAZA_ID AND TR.LANENO=A.LANE_NO ");
            sbSql.Append(" WHERE " + sbWhere.ToString() + " GROUP BY D.SUPPLIER_NAME,D.SPEC_NAME,TR.OBUNO");
            sbCountSql.Append("WITH OBUG AS (" + sbSql.ToString() + ") SELECT COUNT(*) FROM OBUG");
            sbSql.Append(" ORDER BY D.SUPPLIER_NAME ASC");
            string cacheKey = cachePrefix + sbSql.ToString();
            if (pu == null)
            {
                if (CacheManager.Get(cacheKey) == null)
                {
                    using (OdbcConnection conn = ODBCHelper.GetConnection())
                    {
                        rtTable = ODBCHelper.ReadTable(conn, CommandType.Text, sbSql.ToString());
                    }
                    CacheManager.Put(cacheKey, rtTable);
                }
                else
                {
                    rtTable = CacheManager.Get(cacheKey) as DataTable;
                }
            }
            else
            {
                pu.ListSql = sbSql.ToString(); pu.TotalCountSql = sbCountSql.ToString();
                rtTable = pu.GetDb2OdbcPageList();
                cacheKey = cacheKey + "-" + pu.Page.ToString();
                if (CacheManager.Get(cacheKey) == null)
                {
                    CacheManager.Put(cacheKey, rtTable);
                }
                else
                {
                    rtTable = CacheManager.Get(cacheKey) as DataTable;
                    pu.DB2OdbcExecutePage();
                }
            }
            return rtTable;
        }
    }
}
